package com.example.sqlexport.service.impl;

import com.example.sqlexport.entity.DatabaseConfig;
import com.example.sqlexport.entity.SqlExportRequest;
import com.example.sqlexport.service.DatabaseConfigService;
import com.example.sqlexport.service.SqlExportService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.stereotype.Service;

import java.util.List;
import java.util.Map;

@Service
public class SqlExportServiceImpl implements SqlExportService {

    @Autowired
    private DatabaseConfigService databaseConfigService;

    @Override
    public String exportSql(Long databaseConfigId, String tableName, String whereClause) {
        DatabaseConfig config = databaseConfigService.findById(databaseConfigId);
        if (config == null) {
            throw new RuntimeException("数据库配置不存在");
        }

        JdbcTemplate jdbcTemplate = createJdbcTemplate(config);
        
        // 获取表结构
        List<Map<String, Object>> columns = jdbcTemplate.queryForList(
            "SELECT column_name, data_type FROM information_schema.columns WHERE table_schema = ? AND table_name = ? ORDER BY ordinal_position",
            config.getDatabaseName(),
            tableName
        );

        // 构建查询SQL
        StringBuilder querySql = new StringBuilder("SELECT * FROM ").append(tableName);
        if (whereClause != null && !whereClause.trim().isEmpty()) {
            querySql.append(" WHERE ").append(whereClause);
        }

        // 获取表数据
        List<Map<String, Object>> data = jdbcTemplate.queryForList(querySql.toString());

        // 生成INSERT语句
        StringBuilder sql = new StringBuilder();
        for (Map<String, Object> row : data) {
            sql.append("INSERT INTO ").append(tableName).append(" (");
            
            // 添加列名
            boolean first = true;
            for (Map<String, Object> column : columns) {
                String columnName = (String) column.get("column_name");
                if (!first) {
                    sql.append(", ");
                }
                sql.append(columnName);
                first = false;
            }
            
            sql.append(") VALUES (");
            
            // 添加值
            first = true;
            for (Map<String, Object> column : columns) {
                String columnName = (String) column.get("column_name");
                String dataType = (String) column.get("data_type");
                Object value = row.get(columnName);
                
                if (!first) {
                    sql.append(", ");
                }
                
                if (value == null) {
                    sql.append("NULL");
                } else if (dataType.contains("char") || dataType.contains("text") || dataType.contains("date") || dataType.contains("time")) {
                    sql.append("'").append(escapeSql(value.toString())).append("'");
                } else {
                    sql.append(value);
                }
                
                first = false;
            }
            
            sql.append(");\n");
        }

        return sql.toString();
    }

    @Override
    public List<Map<String, Object>> previewData(SqlExportRequest request) {
        DatabaseConfig config = databaseConfigService.findById(request.getDatabaseConfigId());
        if (config == null) {
            throw new RuntimeException("数据库配置不存在");
        }

        JdbcTemplate jdbcTemplate = createJdbcTemplate(config);
        
        // 构建查询SQL
        StringBuilder querySql = new StringBuilder("SELECT * FROM ").append(request.getTableName());
        if (request.getWhereClause() != null && !request.getWhereClause().trim().isEmpty()) {
            querySql.append(" WHERE ").append(request.getWhereClause());
        }
        if (request.getOrderBy() != null && !request.getOrderBy().trim().isEmpty()) {
            querySql.append(" ORDER BY ").append(request.getOrderBy());
        }
        if (request.getLimit() != null && request.getLimit() > 0) {
            querySql.append(" LIMIT ").append(request.getLimit());
        }

        // 获取预览数据
        return jdbcTemplate.queryForList(querySql.toString());
    }

    @Override
    public List<Map<String, Object>> getTableColumns(Long databaseConfigId, String tableName) {
        DatabaseConfig config = databaseConfigService.findById(databaseConfigId);
        if (config == null) {
            throw new RuntimeException("数据库配置不存在");
        }

        JdbcTemplate jdbcTemplate = createJdbcTemplate(config);
        return jdbcTemplate.queryForList(
            "SELECT column_name, data_type FROM information_schema.columns WHERE table_schema = ? AND table_name = ? ORDER BY ordinal_position",
            config.getDatabaseName(),
            tableName
        );
    }

    @Override
    public List<String> getTableNames(DatabaseConfig config) {
        JdbcTemplate jdbcTemplate = createJdbcTemplate(config);
        return jdbcTemplate.queryForList(
            "SELECT table_name FROM information_schema.tables WHERE table_schema = ?",
            String.class,
            config.getDatabaseName()
        );
    }


    public String exportSql(Long databaseConfigId) {
        DatabaseConfig config = databaseConfigService.findById(databaseConfigId);
        if (config == null) {
            throw new RuntimeException("数据库配置不存在");
        }

        StringBuilder sql = new StringBuilder("INSERT INTO database_config (name, host, port, database_name, username, password, remark) VALUES (");
        sql.append("'").append(escapeSql(config.getName())).append("', ");
        sql.append("'").append(escapeSql(config.getHost())).append("', ");
        sql.append(config.getPort()).append(", ");
        sql.append("'").append(escapeSql(config.getDatabaseName())).append("', ");
        sql.append("'").append(escapeSql(config.getUsername())).append("', ");
        sql.append("'").append(escapeSql(config.getPassword())).append("', ");
        sql.append("'").append(escapeSql(config.getRemark())).append("');");

        return sql.toString();
    }

    private String escapeSql(String value) {
        if (value == null) {
            return "";
        }
        return value.replace("'", "''");
    }

    private JdbcTemplate createJdbcTemplate(DatabaseConfig config) {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setUrl(String.format(
            "jdbc:mysql://%s:%d/%s?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai",
            config.getHost(),
            config.getPort(),
            config.getDatabaseName()
        ));
        dataSource.setUsername(config.getUsername());
        dataSource.setPassword(config.getPassword());
        return new JdbcTemplate(dataSource);
    }
} 